﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XeBus.DataAccess
{
    public class ConnectData
    {
        private string ConnectStr = @"Data Source=QUANG\SQLEXPRESS;Initial Catalog=QLXE_BUYT;Persist Security Info=True;User ID=sa;Password=123456";
        private SqlConnection conn;

        public ConnectData()
        {
            conn = new SqlConnection(ConnectStr);
        }

        // Insert, Delete, Update dùng store procedure, mảng biến truyền vào
        public void ExecuteNonQuery(string storedProcedureName, params SqlParameter[] arrParam)
        {

            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }


        public DataTable ExecuteSelectQuery(String _query, SqlParameter[] arrParam)
        {
            DataTable dt = new DataTable();

            SqlCommand cmd = new SqlCommand(_query, conn);
            cmd.CommandType = CommandType.Text;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        // Return datatable
        public DataTable ExecuteDataTable(string storedProcedureName, params SqlParameter[] arrParam)
        {
            DataTable dt = new DataTable();

            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        // Return dataset
        public DataSet ExecuteDataSet(string storedProcedureName, params SqlParameter[] arrParam)
        {
            DataSet Ds = new DataSet();

            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(Ds);
                return Ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        // Thực thi insert, update, delete dùng câu lệnh query
        public void ExecuteNonQueryString(string sql)
        {
            conn = new SqlConnection(ConnectStr);
            conn.Open();

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally { conn.Close(); }
        }

        // Dùng câu lệnh query đổ dữ liệu ra bảng
        public DataTable ExecuteSelectQueryString(string sql)
        {
            conn = new SqlConnection(ConnectStr);
            conn.Open();

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            try
            {
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return dt;
        }
    }
}
